package cn.iocoder.yudao.module.system.dal.mysql.examscore;

import cn.iocoder.yudao.framework.mybatis.core.mapper.BaseMapperX;
import cn.iocoder.yudao.module.system.dal.dataobject.examclass.ExamClassDO;
import cn.iocoder.yudao.module.system.dal.dataobject.examclass.ExamClassStudentDO;
import cn.iocoder.yudao.module.system.dal.dataobject.examscore.ExamScoreDO;
import cn.iocoder.yudao.module.system.dal.dataobject.subjectscoredetail.SubjectScoreDetailDO;
import cn.iocoder.yudao.module.system.dal.dataobject.topscorerecord.TopScoreRecordDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * 成绩导入批量操作 Mapper
 * 提供高性能的批量数据库操作方法
 *
 * @author 芋道源码
 */
@Mapper
public interface ExamScoreMapperBatch extends BaseMapperX<ExamScoreDO> {

    // ==================== 批量查询方法 ====================

    /**
     * 批量查询班级信息
     */
    @Select("<script>" +
            "SELECT * FROM system_exam_class " +
            "WHERE class_name IN " +
            "<foreach collection='classNames' item='className' open='(' separator=',' close=')'>" +
            "#{className}" +
            "</foreach>" +
            "AND deleted = 0" +
            "</script>")
    List<ExamClassDO> selectBatchByClassNames(@Param("classNames") List<String> classNames);

    /**
     * 根据班级名称和学生姓名批量查询学生
     */
    @Select("<script>" +
            "SELECT * FROM system_exam_class_student " +
            "WHERE class_name = #{className} " +
            "AND student_name IN " +
            "<foreach collection='studentNames' item='studentName' open='(' separator=',' close=')'>" +
            "#{studentName}" +
            "</foreach>" +
            "AND deleted = 0" +
            "</script>")
    List<ExamClassStudentDO> selectByClassNameAndStudentNames(
            @Param("className") String className, 
            @Param("studentNames") List<String> studentNames);

    /**
     * 批量查询现有成绩记录（用于重复检测）
     */
    @Select("<script>" +
            "SELECT * FROM system_exam_score " +
            "WHERE exam_id = #{examId} " +
            "AND student_id IN " +
            "<foreach collection='studentIds' item='studentId' open='(' separator=',' close=')'>" +
            "#{studentId}" +
            "</foreach>" +
            "AND deleted = 0" +
            "</script>")
    List<ExamScoreDO> selectByExamIdAndStudentIds(
            @Param("examId") Long examId, 
            @Param("studentIds") List<Long> studentIds);

    // ==================== 批量插入方法 ====================

    /**
     * 批量插入班级信息
     */
    @Insert("<script>" +
            "INSERT INTO system_exam_class (" +
            "class_name, class_code, grade, teacher_id, student_count, " +
            "description, status, creator, create_time, update_time, tenant_id" +
            ") VALUES " +
            "<foreach collection='classes' item='item' separator=','>" +
            "(#{item.className}, #{item.classCode}, #{item.grade}, #{item.teacherId}, " +
            "#{item.studentCount}, #{item.description}, #{item.status}, #{item.creator}, " +
            "#{item.createTime}, #{item.updateTime}, #{item.tenantId})" +
            "</foreach>" +
            "</script>")
    int insertBatchClasses(@Param("classes") List<ExamClassDO> classes);

    /**
     * 批量插入学生信息
     */
    @Insert("<script>" +
            "INSERT INTO system_exam_class_student (" +
            "class_id, student_id, student_no, student_name, class_name, " +
            "exam_no, subject_combination, phone, email, creator, create_time, update_time, tenant_id" +
            ") VALUES " +
            "<foreach collection='students' item='item' separator=','>" +
            "(#{item.classId}, #{item.studentId}, #{item.studentNo}, #{item.studentName}, " +
            "#{item.className}, #{item.examNo}, #{item.subjectCombination}, #{item.phone}, " +
            "#{item.email}, #{item.creator}, #{item.createTime}, #{item.updateTime}, #{item.tenantId})" +
            "</foreach>" +
            "</script>")
    int insertBatchStudents(@Param("students") List<ExamClassStudentDO> students);

    /**
     * 批量插入成绩记录
     */
    @Insert("<script>" +
            "INSERT INTO system_exam_score (" +
            "exam_id, student_id, student_no, student_name, class_id, class_name, " +
            "subject_combination, province, total_score, total_score_assigned, " +
            "chinese_score, math_score, english_score, physics_score, chemistry_score, " +
            "biology_score, politics_score, history_score, geography_score, " +
            "total_union_rank, combination_rank, school_rank, class_rank, grade_rank, " +
            "creator, create_time, update_time, tenant_id" +
            ") VALUES " +
            "<foreach collection='scores' item='item' separator=','>" +
            "(#{item.examId}, #{item.studentId}, #{item.studentNo}, #{item.studentName}, " +
            "#{item.classId}, #{item.className}, #{item.subjectCombination}, #{item.province}, " +
            "#{item.totalScore}, #{item.totalScoreAssigned}, #{item.chineseScore}, #{item.mathScore}, " +
            "#{item.englishScore}, #{item.physicsScore}, #{item.chemistryScore}, #{item.biologyScore}, " +
            "#{item.politicsScore}, #{item.historyScore}, #{item.geographyScore}, " +
            "#{item.totalUnionRank}, #{item.combinationRank}, #{item.schoolRank}, #{item.classRank}, " +
            "#{item.gradeRank}, #{item.creator}, #{item.createTime}, #{item.updateTime}, #{item.tenantId})" +
            "</foreach>" +
            "</script>")
    int insertBatchScores(@Param("scores") List<ExamScoreDO> scores);

    /**
     * 批量插入科目成绩详情
     */
    @Insert("<script>" +
            "INSERT INTO system_subject_score_detail (" +
            "exam_id, student_id, subject_name, original_score, assigned_score, " +
            "grade_level, union_rank, school_rank, class_rank, " +
            "creator, create_time, update_time, tenant_id" +
            ") VALUES " +
            "<foreach collection='details' item='item' separator=','>" +
            "(#{item.examId}, #{item.studentId}, #{item.subjectName}, #{item.originalScore}, " +
            "#{item.assignedScore}, #{item.gradeLevel}, #{item.unionRank}, #{item.schoolRank}, " +
            "#{item.classRank}, #{item.creator}, #{item.createTime}, #{item.updateTime}, #{item.tenantId})" +
            "</foreach>" +
            "</script>")
    int insertBatchSubjectDetails(@Param("details") List<SubjectScoreDetailDO> details);

    /**
     * 批量插入最高分记录
     */
    @Insert("<script>" +
            "INSERT INTO system_top_score_record (" +
            "exam_id, student_id, rank_position, subject_name, original_score, " +
            "assigned_score, union_rank, school_rank, class_rank, " +
            "creator, create_time, update_time, tenant_id" +
            ") VALUES " +
            "<foreach collection='records' item='item' separator=','>" +
            "(#{item.examId}, #{item.studentId}, #{item.rankPosition}, #{item.subjectName}, " +
            "#{item.originalScore}, #{item.assignedScore}, #{item.unionRank}, #{item.schoolRank}, " +
            "#{item.classRank}, #{item.creator}, #{item.createTime}, #{item.updateTime}, #{item.tenantId})" +
            "</foreach>" +
            "</script>")
    int insertBatchTopScoreRecords(@Param("records") List<TopScoreRecordDO> records);

    // ==================== 批量更新方法 ====================

    /**
     * 批量更新成绩记录
     * 使用 CASE WHEN 语句进行批量更新
     */
    @Update("<script>" +
            "UPDATE system_exam_score SET " +
            "total_score = CASE id " +
            "<foreach collection='scores' item='item'>" +
            "WHEN #{item.id} THEN #{item.totalScore} " +
            "</foreach>" +
            "END, " +
            "total_score_assigned = CASE id " +
            "<foreach collection='scores' item='item'>" +
            "WHEN #{item.id} THEN #{item.totalScoreAssigned} " +
            "</foreach>" +
            "END, " +
            "chinese_score = CASE id " +
            "<foreach collection='scores' item='item'>" +
            "WHEN #{item.id} THEN #{item.chineseScore} " +
            "</foreach>" +
            "END, " +
            "math_score = CASE id " +
            "<foreach collection='scores' item='item'>" +
            "WHEN #{item.id} THEN #{item.mathScore} " +
            "</foreach>" +
            "END, " +
            "english_score = CASE id " +
            "<foreach collection='scores' item='item'>" +
            "WHEN #{item.id} THEN #{item.englishScore} " +
            "</foreach>" +
            "END, " +
            "update_time = NOW() " +
            "WHERE id IN " +
            "<foreach collection='scores' item='item' open='(' separator=',' close=')'>" +
            "#{item.id}" +
            "</foreach>" +
            "</script>")
    int updateBatchScores(@Param("scores") List<ExamScoreDO> scores);

    // ==================== 批量删除方法 ====================

    /**
     * 批量删除科目详情（软删除）
     */
    @Update("<script>" +
            "UPDATE system_subject_score_detail SET deleted = 1, update_time = NOW() " +
            "WHERE exam_id = #{examId} AND student_id IN " +
            "<foreach collection='studentIds' item='studentId' open='(' separator=',' close=')'>" +
            "#{studentId}" +
            "</foreach>" +
            "</script>")
    int deleteBatchSubjectDetails(@Param("examId") Long examId, @Param("studentIds") List<Long> studentIds);

    /**
     * 批量删除最高分记录（软删除）
     */
    @Update("<script>" +
            "UPDATE system_top_score_record SET deleted = 1, update_time = NOW() " +
            "WHERE exam_id = #{examId} AND student_id IN " +
            "<foreach collection='studentIds' item='studentId' open='(' separator=',' close=')'>" +
            "#{studentId}" +
            "</foreach>" +
            "</script>")
    int deleteBatchTopScoreRecords(@Param("examId") Long examId, @Param("studentIds") List<Long> studentIds);

    // ==================== 统计查询方法 ====================

    /**
     * 统计考试中的学生数量
     */
    @Select("SELECT COUNT(*) FROM system_exam_score WHERE exam_id = #{examId} AND deleted = 0")
    int countStudentsByExamId(@Param("examId") Long examId);

    /**
     * 统计班级中的学生数量
     */
    @Select("SELECT COUNT(*) FROM system_exam_class_student WHERE class_id = #{classId} AND deleted = 0")
    int countStudentsByClassId(@Param("classId") Long classId);

    /**
     * 批量统计各班级学生数量
     */
    @Select("<script>" +
            "SELECT class_id, COUNT(*) as student_count " +
            "FROM system_exam_class_student " +
            "WHERE class_id IN " +
            "<foreach collection='classIds' item='classId' open='(' separator=',' close=')'>" +
            "#{classId}" +
            "</foreach>" +
            "AND deleted = 0 " +
            "GROUP BY class_id" +
            "</script>")
    List<ClassStudentCount> countStudentsByClassIds(@Param("classIds") List<Long> classIds);

    /**
     * 班级学生数量统计结果
     */
    class ClassStudentCount {
        private Long classId;
        private Integer studentCount;

        public Long getClassId() { return classId; }
        public void setClassId(Long classId) { this.classId = classId; }

        public Integer getStudentCount() { return studentCount; }
        public void setStudentCount(Integer studentCount) { this.studentCount = studentCount; }
    }

    // ==================== 性能优化查询 ====================

    /**
     * 使用 INSERT ... ON DUPLICATE KEY UPDATE 进行批量插入或更新
     * 适用于有唯一键约束的表
     */
    @Insert("<script>" +
            "INSERT INTO system_exam_score (" +
            "exam_id, student_id, student_no, student_name, class_id, " +
            "total_score, total_score_assigned, subject_combination, " +
            "creator, create_time, update_time, tenant_id" +
            ") VALUES " +
            "<foreach collection='scores' item='item' separator=','>" +
            "(#{item.examId}, #{item.studentId}, #{item.studentNo}, #{item.studentName}, " +
            "#{item.classId}, #{item.totalScore}, #{item.totalScoreAssigned}, " +
            "#{item.subjectCombination}, #{item.creator}, #{item.createTime}, " +
            "#{item.updateTime}, #{item.tenantId})" +
            "</foreach>" +
            "ON DUPLICATE KEY UPDATE " +
            "total_score = VALUES(total_score), " +
            "total_score_assigned = VALUES(total_score_assigned), " +
            "subject_combination = VALUES(subject_combination), " +
            "update_time = VALUES(update_time)" +
            "</script>")
    int insertOrUpdateBatchScores(@Param("scores") List<ExamScoreDO> scores);

    /**
     * 批量查询考试成绩（带分页）
     */
    @Select("<script>" +
            "SELECT * FROM system_exam_score " +
            "WHERE exam_id = #{examId} AND deleted = 0 " +
            "ORDER BY total_score DESC " +
            "LIMIT #{offset}, #{limit}" +
            "</script>")
    List<ExamScoreDO> selectScoresByExamIdWithPaging(
            @Param("examId") Long examId, 
            @Param("offset") int offset, 
            @Param("limit") int limit);

    /**
     * 批量更新班级学生数量
     */
    @Update("<script>" +
            "<foreach collection='updates' item='item' separator=';'>" +
            "UPDATE system_exam_class SET student_count = #{item.studentCount}, " +
            "update_time = NOW() WHERE id = #{item.classId}" +
            "</foreach>" +
            "</script>")
    int updateBatchClassStudentCount(@Param("updates") List<ClassStudentCount> updates);
}